package cm2bug;

use DBI;

my $cm2bug_errcode=0;
my $cm2bug_errstr=undef;

#########################################################################
# Records is an array of known record types with details of each field	#
# including whether it is optional or not, and what kind of validation	#
# and manipulation to apply when attempting to add records via the	#
# add_verified_record method.						#
#########################################################################

my %records=(
	"users"	=> {
		"username"	=> ["m",\&is_unique,\&is_nowhitespace],
		"desc"		=> ["m"],
		"mailid"	=> ["o",\&is_mailid],
		"password"	=> ["o",\&encode_password]
		},
	"groups" => {
		"groupname"	=> ["m",\&is_unique,\&is_nowhitespace],
		"desc"		=> ["m"],
		"users"		=> ["m",\&is_user_list]
		},
	"projects" => {
		"projectname"	=> ["m",\&is_unique,\&is_nowhitespace,\&is_unixname],
		"desc"		=> 	["m"],
		"patch_emails"	=> 	["o",\&is_yesno],
		"patch_emails_to" => 	["o",\&is_mailid]
		},
	"bugs"	=> {
		"bug"	=> 		["m",\&is_unique],
		"type"	=> 		["m",\&is_valid_bugtype],
		"raised_date" => 	["m",\&is_valid_date],
		"raised_by" =>		["m",\&is_valid_user],
		"inform_users" =>	["o",\&is_user_list],
		"inform_groups" =>	["o",\&is_group_list],
		"desc" =>		["o"],
		"projects_affected" =>	["m",\&is_project_list],
		"assigned_to_users" =>	["o",\&is_user_list],
		"assigned_to_groups" => ["o",\&is_group_list],
		"parent" =>		["o",\&is_existing_bug],
		"state" =>		["o",\&is_valid_state],
		"time_est" =>		["o",\&is_number],
		"projects_tag" =>	["o"],
		}
);

sub new {
my $proto = shift;
my $class = ref($proto) || $proto;
my $self  = {};

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$self->{DBH}=DBI->connect("dbi:SQLite:dbname=$_[0]","","",
			{RaiseError => 0,PrintError => 0,AutoCommit => 0});
	if(!defined($self->{DBH})) {
		$cm2bug_errcode=1;
		$cm2bug_errstr="Unable to connect to database $_[0].";
		return undef;
	}
	$self->{FILENAME}=$_[0];
	bless($self,$class);
	return $self;
}

# Class method 
sub error {
	return($cm2bug_errcode,$cm2bug_errstr);

}

# Instance method to execute any old SQL

sub do($$) {
my $self=shift;

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$self->{DBH}->do($_[0]);
	my $rc=$self->{DBH}->errstr;
	if(defined($rc)) {
		$cm2bug_errcode=2;
		$cm2bug_errstr=$rc;
	}
}

sub commit($) {
my $self=shift;

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$self->{DBH}->commit;
	my $rc=$self->{DBH}->errstr;
	if(defined($rc)) {
		$cm2bug_errcode=3;
		$cm2bug_errstr=$rc;
	}
}

sub disconnect {
my $self=shift;

	if(defined($self->{DBH})) {
		$self->{DBH}->disconnect;
	}
}

sub DESTROY {
my $self=shift;

	if(defined($self->{DBH})) {
		$self->{DBH}->disconnect;
	}
}

sub exists($$$) {
my $self=shift;
my ($type,$obj)=@_;
my ($cmd,$rc,$sth);

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$type="table" if !defined($type);
	$cmd="SELECT * FROM SQLITE_MASTER WHERE type = '$type' AND name = '$obj';";
	$sth=$self->{DBH}->prepare($cmd);
	if(!defined($sth)) {
		$rc=$self->{DBH}->errstr;
		$cm2bug_errcode=4;
		$cm2bug_errstr=$rc;
		return 0;
	}
	$sth->execute();
	if(!defined($sth->fetchrow_hashref)) {
		return 0;
	}
	return 1;
}

sub get_rows($$) {
my ($self,$query)=@_;
my ($sth,$rc,@R,$aref);

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$sth=$self->{DBH}->prepare($query);
	if(!defined($sth)) {
		$rc=$self->{DBH}->errstr;
		$cm2bug_errcode=5;
		$cm2bug_errstr=$rc;
		return undef;
	}
	$sth->execute();
	@R=();
	while($aref  = $sth->fetchrow_hashref) {
		push @R,{%$aref};
	}
	return \@R;
}

sub get_object_schema($$$) {
my ($self,$type,$obj)=@_;
my ($cmd,$aref,$sth,$rc);

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$type="table" if !defined($type);
	if(!defined($obj)) {
		$cm2bug_errcode=6;
		$cm2bug_errstr="Specified object value is undefined.";
		return undef;
	}
	$cmd="SELECT * FROM SQLITE_MASTER WHERE type = '$type' AND name = '$obj';";
	$sth=$self->{DBH}->prepare($cmd);
	if(!defined($sth)) {
		$rc=$self->{DBH}->errstr;
		$cm2bug_errcode=7;
		$cm2bug_errstr=$rc;
		return undef;
	}
	$sth->execute();
	if(!defined($aref=$sth->fetchrow_hashref)) {
		$cm2bug_errcode=8;
		$cm2bug_errstr="$obj of type $type does not exist.";
		return undef;
	}
	return $aref->{sql};
}

sub get_record_requirements {
my ($crec,$str);

	$str="";
	for $crec (sort(keys %records)) {
		$str.=sprintf("  * %-20s ::= ",$crec);
		my $ckey;
		for $ckey (keys(%{$records{$crec}})) {
			if($records{$crec}->{$ckey}->[0] eq "m") {
				$str.="$ckey ";
			}
		}
		for $ckey (keys(%{$records{$crec}})) {
			if($records{$crec}->{$ckey}->[0] eq "o") {
				$str.="[$ckey] ";
			}
		}
		$str.="\n";
	}
	return $str;
}

sub add_verified_record {
my $self=shift;
my $ARG_table=shift;
my @ARGV=@_;
local $DBH;		# For use in verifiation sub-routines
my %VALUES=();

	$cm2bug_errcode=0;
	$cm2bug_errstr=undef;
	$DBH=$self->{DBH};

	my ($code,$val,$rc);
	if(!exists($records{$ARG_table})) {
			$cm2bug_errcode=10;
			$cm2bug_errstr="Add_validated_record does not work on table '$ARG_table'.";
			return 0;
	}
	foreach (@ARGV) {
		my $carg=$_;
		my ($field,$value)=split(/:/,$carg);

		if(!exists($records{$ARG_table}->{$field})) {
			$cm2bug_errcode=10;
			$cm2bug_errstr="Column '$field' is not defined in table '$ARG_table'.";
			return 0;
		}

		#################################################################
		# If the field has one or more validation, formation tools,	#
		# then call it now.						#
		#################################################################

		for $code ((@{$records{$ARG_table}->{$field}})[1..9]) {
			next if !defined($code);
			next if $code eq "";
			($rc,$val)=&$code($ARG_table,$field,$value);
			if($rc) {
				$cm2bug_errcode=11;
				$cm2bug_errstr="$val";
				return 0;
			}
			$value=$val if defined($val);
		}
		$VALUES{$field}=$value;
	}

	#################################################################
	# Check to ensure that all mandatory fields have been specified	#
	# and if not then the process should abort with suitable error.	#
	#################################################################

	my @B=();
	my $ckey;
	for $ckey (keys(%{$records{$ARG_table}})) {
		if($records{$ARG_table}->{$ckey}->[0] eq "m" &&
			!exists($VALUES{$ckey})) {
			push @B,$ckey;
		}
	}
	if(@B) {
		$cm2bug_errcode=11;
		$cm2bug_errstr="Mandatory columns missing for new row on table '$ARG_table': ".join(",",@B);
		return 0;
	}

	#################################################################
	# Everything should now be in place to actually add the		#
	# record.							#
	#################################################################
	
	my $cmd="INSERT INTO $ARG_table(";
	$cmd.=join(",",(keys %VALUES));
	$cmd.=") VALUES(";
	my @L=map { "'$_'"; } (values %VALUES);
	$cmd.=join(",",@L) . ");";

	$DBH->do($cmd);
	$rc=$DBH->errstr;
	if(defined($rc)) {
		$cm2bug_errcode=12;
		$cm2bug_errstr="Error occured whilst performing:\n$cmd\n\nError: $rc\n";
		return 0;
	}
	$DBH->commit;
	return 1;
}

#########################################################################
# Below are verification methods that should not be used directly -	#
# they are called via the add_verified_record.				#
#########################################################################

sub is_unique($$$) {
my ($table,$field,$value)=@_;
my ($sth,$cmd,$rc);

	$cmd="SELECT * FROM $table WHERE $field = '$value';";
	$sth=$DBH->prepare($cmd);
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table $table:\n$rc");
	}
	if(! $sth->execute()) {
		return(1,"Unable to execute '$cmd':\n".$DBH->errstr);
	}
	if(defined($sth->fetchrow_hashref)) {
		return(1,"Value '$value' already exists in column '$field' in table '$table'.");
	}
	return(0,undef);
}

sub is_nowhitespace($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /\s+/) {
		return(1,"Value '$value' for column '$field' must not contain white space.");
	}
	return(0,undef);
}

sub is_mailid($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /^[\w\-\.]+@([\w\-]+\.{0,1}){1,4}$/i &&
		$value =~ /[^\.]+$/) {
		return(0,undef);
	}
	return(1,"Value '$value' is not a valid Internet email address.");
}

sub encode_password($$$) {
my ($table,$field,$value)=@_;

	my $ctx=Digest::SHA1->new;
	$ctx->add($value);
	my $hex=$ctx->hexdigest;
	return(0,$hex);
}

#########################################################################
# Ensure that a comma-separated list of users is valid - and sort and	#
# remove any duplicates from the list.					#
#########################################################################

sub is_user_list($$$) {
my ($table,$field,$value)=@_;
my ($cmd,$rc,$sth,@R,$aref,%UL,@B,%US);

	$cmd="SELECT username FROM users;";
	$sth=$DBH->prepare($cmd);
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table $table:\n$rc");
	}
	%UL=();
	$sth->execute();
	while($aref=$sth->fetchrow_hashref) {
		$UL{$aref->{username}}=1;
	}

	@R=split(/,/,$value);
	map {$US{$_}=1;} @R;
	@B=();
	foreach (@R) {
		if(!exists($UL{$_})) {
			push @B,$_;
		}
	}
	if(@B) {
		return(1,"Following usernames are unknown: ".join(",",@B));
	}
	return(0,join(",",sort(keys %US)));
}

sub is_unixname($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /^[\w\-]+$/) {
		return(0,undef);
	}
	return(1,"Value '$value' is not a valid UNIX filename.");
}

sub is_yesno($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /^(y|yes|n|no)$/i) {
		return(0,uc(substr($value,0,1)));
	}
	return(1,"Value '$value' is not valid for a 'yes/no' column.");
}

sub is_valid_user($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /,/) {
		return(1,"Column '$field' in table '$table' can not be a list.");
	}
	my ($a,$b)=is_user_list($table,$field,$value);
	return($a,$b) if $a;
	return (0,undef);
}

sub is_valid_bugtype($$$) {
my ($table,$field,$value)=@_;
my ($sth,$rc);

	$value=uc($value);
	$sth=$DBH->prepare("SELECT * FROM bugtypes WHERE UPPER(type) = '$value'");
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table $table:\n$rc");
	}
	$sth->execute();
	if(defined($sth->fetchrow_hashref)) {
		return(0,ucfirst($value));
	}
	return(1,"Value '$value' is invalid value for column '$field' in table '$table'.");
}

sub is_valid_date($$$) {
my ($table,$field,$value)=@_;

	if($value =~ m{^\d{2}/\d{2}/\d{4}$}) {
		return(0,undef);
	}
	return(1,"Date must be in format 'DD/MM/YYYY'");
}

#########################################################################
# Here we validate that the field is a list and each comma-separated	#
# element is the name of an existing project.				#
#########################################################################

sub is_project_list($$$) {
my ($table,$field,$value)=@_;
my (%PR,@F,$sth,$rc,$aref);

	$sth=$DBH->prepare("SELECT projectname FROM projects");
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table 'projects':\n$rc");
	}
	$sth->execute();
	while($aref=$sth->fetchrow_hashref) {
		$PR{$aref->{projectname}}=1;
	}

	#################################################################
	# %PR now contains the names of all projects that are		#
	# currently defined.						#
	#################################################################

	@F=split(/,/,$value);
	if(!scalar(@F)) {
		return(1,"Value '$value' for column '$field' is invalid for table '$table'.");
	}
	my %OK=();
	my %BAD=();
	foreach (@F) {
		if(!exists($PR{$_})) {
			$BAD{$_}=1;
		} else {
			$OK{$_}=1;
		}
	}
	if(scalar(keys %BAD)) {
		return(1,"Following project names unknown: ".join(",",sort(keys %BAD)));
	}
	return(0,sort(keys %OK));
}

#########################################################################
# Ensure that a comma-separated list of groups is valid - and sort and	#
# remove any duplicates from the list.					#
#########################################################################

sub is_group_list($$$) {
my ($table,$field,$value)=@_;
my ($cmd,$rc,$sth,@R,$aref,%GL,@B,%US);

	$cmd="SELECT groupname FROM groups;";
	$sth=$DBH->prepare($cmd);
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table 'groups':\n$rc");
	}
	%GL=();
	$sth->execute();
	while($aref=$sth->fetchrow_hashref) {
		$GL{$aref->{groupname}}=1;
	}

	@R=split(/,/,$value);
	map {$US{$_}=1;} @R;
	@B=();
	foreach (@R) {
		if(!exists($GL{$_})) {
			push @B,$_;
		}
	}
	if(@B) {
		return(1,"Following groupnames are unknown: ".join(",",@B));
	}
	return(0,join(",",sort(keys %US)));
}

#########################################################################
# Ensure that a comma-separated list of groups is valid - and sort and	#
# remove any duplicates from the list.					#
#########################################################################

sub is_bug_list($$$) {
my ($table,$field,$value)=@_;
my ($cmd,$rc,$sth,@R,$aref,%TL,@B,%US);

	$cmd="SELECT number FROM bug;";
	$sth=$DBH->prepare($cmd);
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table 'bug':\n$rc");
	}
	%TL=();
	$sth->execute();
	while($aref=$sth->fetchrow_hashref) {
		$TL{$aref->{number}}=1;
	}

	@R=split(/,/,$value);
	map {$US{$_}=1;} @R;
	@B=();
	foreach (@R) {
		if(!exists($TL{$_})) {
			push @B,$_;
		}
	}
	if(@B) {
		return(1,"Following bug numbers are unknown: ".join(",",@B));
	}
	return(0,join(",",sort(keys %US)));
}

sub is_valid_state($$$) {
my ($table,$field,$value)=@_;

	if(scalar(grep {/^$value$/i} qw(complete cancelled open))) {
		return(0,uc($value));
	}
	return(1,"Value '$value' is not a suitable state.");
}

sub is_number($$$) {
my ($table,$field,$value)=@_;

	if($value =~ /^\d+$/ || $value =~ /\d*\.\d+$/) {
		return(0,undef);
	}
	return(1,"Value '$value' is not a valid number.");
}

sub is_existing_bug($$$) {
my ($table,$field,$value)=@_;
my ($cmd,$rc,$sth,@R,$aref,%TL,@B,%US);

	$cmd="SELECT bug FROM $table WHERE bug = '$value';";
	$sth=$DBH->prepare($cmd);
	if(!defined($sth)) {
		$rc=$DBH->errstr;
		return(1,"Error attempting to query table 'bug':\n$rc");
	}
	$sth->execute();
	$aref=$sth->fetchrow_hashref;
	return(0,undef) if defined($aref);
	return(1,"Bug '$value' is not defined in table '$table'.");
}
1;

